import { Service } from 'egg';
import * as Jsapi from 'wechat_interaction_jsapi';
import * as fs from 'fs';
import * as request from 'request';
import * as path from 'path';
import * as moment from 'moment';
import * as ejsExcel from 'ejsexcel';


export default class UploadService extends Service {
    /**
     * 从微信服务器下载图片
     * @param param 
     */
    public async uploadImage(param: any) {
        try {
            const _this = this;
            //保存图片的url
            const imgName = this.ctx.helper.genImgName() + '.jpg';
            let url = path.join(__dirname, '../public/img/', imgName);
            const { weixin } = this.app.config;
            const jssdk = new Jsapi(weixin.appId, weixin.appSecret);
            const uploadUrl = `https://api.weixin.qq.com/cgi-bin/media/get?access_token=${await jssdk.getAccessToken()}&media_id=${param.serverId}`;
            //获取图片并保存
            const result: any = request({
                method: 'GET',
                url: uploadUrl
            });
            result.pipe(fs.createWriteStream(url));
            let result2: any = await new Promise((resolve) => {
                result.on("end", function () {
                    // 查看上传的是否是图片
                    let res = fs.readFileSync(url, 'utf-8');
                    if (String(res).length < 200) {
                        _this.ctx.logger.error(String(res));
                        try {
                            fs.unlinkSync(url);
                        } catch (e) {
                            _this.ctx.logger.error(e);
                        }
                        resolve({ code: 2, msg: '上传失败,请重新上传' });
                    } else {
                        resolve({ code: 1, msg: '上传图片成功', data: { url: imgName } });
                    }
                });
                result.on("error", function () {
                    resolve({ code: 2, msg: '上传失败' });
                });
            });
            if (result2.code === 1) {
                return { url: result2.data.url };
            } else {
                throw new Error(JSON.stringify({ code: 2, msg: '上传失败' }));
            }
        } catch (e) {
            throw new Error(JSON.stringify({ code: 2, msg: '上传失败' }));
        }
    }

    /**
     * 导出excel职员表
     * @param param 
     */
    public async exportEmployeeExcel(param: any) {
        try {
            let queryStr = `
                    SELECT
                        e.employee_id,
                        e.employee_name,
                        e.employee_account,
                        w.work_unit_name,
                        d.department_name,
                        p.position_name,
                        e.created_at,
                        e.employee_status 
                    FROM
                        t_employee e
                        INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                        AND w.is_delete = '0'
                        INNER JOIN t_department d ON e.department_id = d.department_id 
                        AND d.is_delete = '0'
                        INNER JOIN t_position p ON e.position_id = p.position_id 
                        AND p.is_delete = '0' 
                    WHERE
                        e.is_delete = '0'`;
            const actualParam: any = {};
            if (param.work_unit_id) {
                queryStr += ` and e.work_unit_id=:work_unit_id`;
                actualParam.work_unit_id = param.work_unit_id;
            }
            if (param.department_id) {
                queryStr += ` and e.department_id=:department_id`;
                actualParam.department_id = param.department_id;
            }
            if (param.position_id) {
                queryStr += ` and e.position_id=:position_id`;
                actualParam.position_id = param.position_id;
            }
            if (param.register_start_time && param.register_end_time) {
                queryStr += ` and e.created_at between :register_start_time and :register_end_time`;
                actualParam.register_start_time = param.register_start_time + ` 00:00:00`;
                actualParam.register_end_time = param.register_end_time + ` 23:59:59`;
            }
            if (param.employee_status) {
                queryStr += ` and e.employee_status=:status`;
                actualParam.status = param.employee_status;
            }
            if (param.employee_name) {
                queryStr += ` and (e.employee_name like :employee_name or e.employee_account like :employee_name)`;
                actualParam.employee_name = '%' + param.employee_name + '%';
            }
            queryStr += ` order by e.created_at desc`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: actualParam
            });
            const list: any = [];
            for (let i = 0; i < result.length; i++) {
                const item: any = {};
                //item.employee_id = result[i].employee_id;
                item.employee_name = result[i].employee_name;
                item.employee_account = result[i].employee_account;
                item.work_unit_name = result[i].work_unit_name;
                item.department_name = result[i].department_name;
                item.position_name = result[i].position_name;
                item.created_at = moment(result[i].created_at).format('YYYY-MM-DD HH:mm:ss');
                const employee_status = result[i].employee_status;
                let status_text = '';
                if (employee_status == 1) {
                    status_text = '待审核';
                } else if (employee_status == 2) {
                    status_text = '已通过';
                } else if (employee_status == 3) {
                    status_text = '不通过';
                } else if (employee_status == 4) {
                    status_text = '已停用';
                } else {
                    status_text = '未知状态';
                }
                item.employee_status = status_text;
                list.push(item);
            }
            let resultList: any = [];
            resultList.push(list);
            const exlBuf = fs.readFileSync(path.join(__dirname, '../public/', 'activityEnrollList.xlsx'));
            const now = moment().format('YYYYMMDDHHmmss');
            const excelData = await new Promise((resolve, reject) => {
                ejsExcel.renderExcelCb(exlBuf, resultList, function (err, exlBuf2) {
                    if (err) {
                        reject(err);
                    }
                    resolve(exlBuf2);
                })
            })
            return { name: "职员表-" + now + '.xlsx', data: excelData };
        } catch (e) {
            throw new Error(JSON.stringify({ code: 3, msg: e.message }));
        }
    }

    /**
     * 导出打分表
     * @param param 
     */
    public async exportGradeList(param: any) {
        try {
            const { project_id } = param;
            const queryStr = `select e.employee_id,e.employee_name from t_grade_employee g inner join t_employee e on g.project_id=:project_id and 
            g.is_delete='0' and g.employee_id=e.employee_id order by e.created_at desc`;
            const employeeList: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id }
            });
            const queryStr1 = `select e.employee_id,e.employee_name from t_grade_target g inner join t_employee e on g.project_id=:project_id and 
            g.is_delete='0' and g.employee_id=e.employee_id order by e.created_at desc`;
            const targetEmployeeList: any = await this.ctx.model.query(queryStr1, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id }
            });
            const returnData: any = [];
            for (let i = 0; i < employeeList.length; i++) {
                const employee_id = employeeList[i].employee_id;
                const gradeResult: any = await this.ctx.model.GradeResult.findAll({ attributes: ['target_employee_id', 'grade_result_score'], where: { project_id, employee_id, is_delete: '0' } });
                const item: any = {};
                item.employee_id = employee_id;
                item.employee_name = employeeList[i].employee_name;
                const item1: any = [];
                for (let j = 0; j < targetEmployeeList.length; j++) {
                    const target_employee_id = targetEmployeeList[j].employee_id;
                    if (gradeResult.length > 0) {
                        let hasData=false;
                        for (let k = 0; k < gradeResult.length; k++) {
                            const item2: any = {};
                            if (gradeResult[k].target_employee_id === target_employee_id) {
                                item2.emplpyee_id = target_employee_id;
                                item2.grade_result_score = gradeResult[k].grade_result_score;
                                item1.push(item2);
                                hasData=true;
                                break;
                            }
                        }
                        if(!hasData){
                            const item2: any = {};
                            item2.emplpyee_id = target_employee_id;
                            item2.grade_result_score = 0;
                            item1.push(item2);
                        }
                    } else {
                        const item2: any = {};
                        item2.employee_id = target_employee_id;
                        item2.grade_result_score = 0;
                        item1.push(item2);
                    }
                }
                item.result = item1;
                returnData.push(item);
            }
            let allScore:any=[];
            for(let i=0;i<returnData.length;i++){
                const result=returnData[i].result;
                for(let j=0;j<result.length;j++){
                    let currentScore:any=allScore[j];
                    const grade_result_score=result[j].grade_result_score;
                    if(!currentScore||currentScore<=0){
                        currentScore=grade_result_score;
                    }else{
                        currentScore+=grade_result_score;
                    }
                    allScore[j]=currentScore;
                }
            }
            let resultList: any = [];
            resultList.push(returnData);
            resultList.push(targetEmployeeList);
            resultList.push(allScore);
            const exlBuf = fs.readFileSync(path.join(__dirname, '../public/', 'gradeList.xlsx'));
            const now = moment().format('YYYYMMDDHHmmss');
            const excelData = await new Promise((resolve, reject) => {
                ejsExcel.renderExcelCb(exlBuf, resultList, function (err, exlBuf2) {
                    if (err) {
                        reject(err);
                    }
                    resolve(exlBuf2);
                })
            })
            return { name: "打分表-" + now + '.xlsx', data: excelData };
        } catch (e) {
            throw new Error(JSON.stringify({ code: 3, msg: e.message }));
        }
    }
}